package com.decent.ejfadmin.collectcard.dao.provider;

import com.decent.ejfadmin.collectcard.entity.SupplyCardQueryBean;
import com.decent.ejfadmin.common.bean.SupplyCard;
import com.decent.ejfadmin.common.enums.PlatEnum;
import com.decent.ejfadmin.common.enums.SupplyCardStatus;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;
import java.util.Objects;

/**
 * @author weicx
 * @date 2018/07/17
 */
public class CollectCardDetailDaoProvider {

    /**
     * 收卡明细查询计数
     *
     * @param params 参数
     * @return sql
     */
    public String queryReceiveListCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select count(1) ");
        generateSupplyCardApiQuerySql(sql, params);
        return sql.toString();
    }

    /**
     * 收卡明细查询组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateSupplyCardApiQuerySql(StringBuffer sql, HashMap<String, Object> params) {
        sql.append(" FROM " +
                "     yuhuang_supply_card sc  where 1=1 ");
        //会员
        SupplyCardQueryBean supplyCardQueryBean = (SupplyCardQueryBean) params.get("su");
        if (Integer.valueOf(0).equals(supplyCardQueryBean.getTimeType())) {
            sql.append(" and sc.startTime >= #{su.startTime} and sc.startTime <= #{su.endTime}  ");
        } else {
            sql.append(" and  sc.endTime >= #{su.startTime} and sc.endTime <= #{su.endTime}    ");
        }
        String ids = supplyCardQueryBean.getIds();
        if (StringUtils.isNotBlank(ids)) {
            sql.append(" and sc.userId in (").append(ids).append(") ");
        }
        if (StringUtils.isNotBlank(supplyCardQueryBean.getOrderId())) {
            sql.append(" and sc.orderId = #{su.orderId} ");
        }

        if (Objects.nonNull(supplyCardQueryBean.getTransferMark())) {
            if (Integer.valueOf(1).equals(supplyCardQueryBean.getTransferMark())) {
                sql.append(" and sc.transfer_mark = #{su.transferMark} ");
            } else {
                sql.append(" and sc.transfer_mark is null ");
            }
        }

        if (StringUtils.isNotBlank(supplyCardQueryBean.getRemark())) {
            sql.append(" and instr(sc.remark,#{su.remark})>0 ");
        }
        if (StringUtils.isNotBlank(supplyCardQueryBean.getTeeEggOrderNo())) {
            sql.append(" and teeEggOrderNo=#{su.teeEggOrderNo} ");
        }

        if (Objects.nonNull(supplyCardQueryBean.getAgentNo())) {
            //易缴费
            if (supplyCardQueryBean.getAgentNo().equals(PlatEnum.EJF.getCode())) {
                sql.append(" and sc.agentNo in(14165,13950) ");
            } else if (supplyCardQueryBean.getAgentNo().equals(PlatEnum.EASY_SERVER.getCode())) {
                //易服务
                sql.append(" and sc.agentNo in(13630,14350,18370) ");
            } else if (supplyCardQueryBean.getAgentNo().equals(PlatEnum.VIRTUAL.getCode())) {
                sql.append(" and sc.productType in(15,16,20) and sc.status=1 ");
            } else if (supplyCardQueryBean.getAgentNo().equals(PlatEnum.FUEL_CARD.getCode())) {
                sql.append(" and sc.productType in(300,302,303,310) and sc.status=1 ");
            }
        }
        //增加card来源
        if (Objects.nonNull(supplyCardQueryBean.getCardSource())) {
        	 sql.append(" and cardSource=#{su.cardSource} ");
        }
        if (StringUtils.isNotBlank(supplyCardQueryBean.getStatusStr())) {
            //状态
            if (String.valueOf(SupplyCardStatus.makeup.getStatus()).equals(StringUtils.removeStart(supplyCardQueryBean.getStatusStr(), ","))) {
                //补结算
                sql.append(" and sc.status = 1 and sc.added = 1 ");
            } else {
                sql.append(" and sc.status in( ").append(StringUtils.removeStart(supplyCardQueryBean.getStatusStr(), ",")).append(" ) ");
            }
        }
        
        
        SupplyCard card = (SupplyCard) params.get("card");
        if (card != null) {
            //卡号
            if (StringUtils.isNotBlank(card.getCardNumber())) {
                sql.append(" and sc.cardNumber = #{card.cardNumber} ");
            }
            //卡密
            if (StringUtils.isNotBlank(card.getCardPwd())) {
                sql.append(" and sc.cardPwd = #{card.cardPwd} ");
            }
            //批次号
            if (StringUtils.isNotBlank(card.getBatchId())) {
                sql.append(" and sc.batchId = #{card.batchId} ");
            }
            //面值
            if (card.getFace() != null) {
                sql.append(" and sc.face = #{card.face} ");
            }
            //运营商
            if (card.getProductType() != null) {
                sql.append(" and sc.productType = #{card.productType} ");
            }
            //地区
            if (StringUtils.isNotBlank(card.getArea())) {
                sql.append(" and sc.area = #{card.area} ");
            }
            sql.append(" ORDER BY " +
                    "     sc.startTime DESC, " +
                    "     sc.id DESC");
        }
    }

    /**
     * 收卡明细查询分页
     *
     * @param params 参数
     * @return sql
     */
    public String queryReceiveListPage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT " +
                " t1.*, u.account userAccount, " +
                " i.`name` userName, " +
                " " +
                "IF ( " +
                " p.state = 2 " +
                " AND t1.`STATUS` = 4, " +
                " 1, " +
                " 0 " +
                ") maintenance," +
                " p.product_name productName," +
                " p.card_operator_name cardOperatorName " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   t.*, t.settlePrice * t.discount userPrice, t.discount as equityUserPrice, " +
                "   t.settlePrice as equitySettlePrice " +
                "  FROM " +
                "   ( " +
                "    SELECT " +
                "     sc.id, " +
                "     sc.userId, " +
                "     sc.cardNumber, " +
                "     sc.cardPwd, " +
                "     sc. STATUS, " +
                "     sc.batchId, " +
                "     sc.orderId, " +
                "     sc.face,sc.cardSource, " +
                "     DATE_FORMAT( " +
                "      sc.startTime, " +
                "      '%Y-%m-%d %H:%i:%S' " +
                "     ) startTime, " +
                "     DATE_FORMAT( " +
                "      sc.endTime, " +
                "      '%Y-%m-%d %H:%i:%S' " +
                "     ) endTime, " +
                "     sc.productType, " +
                "     sc.added, " +
                "     sc.settlePrice, " +
                "     sc.userPrice discount, " +
                "     sc.failReason, " +
                "     sc.judgeFace, " +
                "     sc.effectTime, " +
                "     sc.agentNo, " +
                "     sc.mobileNo, " +
                "     sc.area, sc.productId," +
                "    IF ( " +
                "     sc.supplyArea = 0, " +
                "     '全国', " +
                "     sc.area " +
                "    ) AS supplyArea," +
                "      sc.remark," +
                " sc.aliKey,sc.teeEggOrderNo ");
        generateSupplyCardApiQuerySql(sql, params);
        sql.append(" ) t ");
        sql.append(" limit #{page.beginIndex},#{page.size}  ");
        sql.append(" ) t1 LEFT JOIN (SELECT\n" +
                " p.product_type,\n" +
                " p.area,\n" +
                " p.face,\n" +
                " p.state,\n" +
                " p.product_name,\n" +
                " o.card_operator_name\n" +
                " FROM\n" +
                " yuhuang_supply_product p,yuhuang_card_operator o where p.product_type=o.card_operator_id\n" +
                " GROUP BY\n" +
                " p.face,\n" +
                " p.area,\n" +
                " p.product_type) p ON p.product_type = t1.productType\n" +
                "AND p.area = t1.supplyArea " +
                "AND p.face = t1.face, " +
                " yuhuang_sup_user u, " +
                " yuhuang_sup_info i  " +
                "WHERE " +
                " t1.userId = u.user_id " +
                "AND t1.userId = i.user_id  ");
        return sql.toString();
    }

    /**
     * 按照内置id、批次号查询预判面值不符的收卡订单
     *
     * @param params 参数
     * @return sql
     */
    public String queryWrongFaceCard(HashMap<String, String> params) {
        StringBuilder sql = new StringBuilder(100);
        sql.append("select status,orderId,userId,productType from yuhuang_supply_card where status in (5,6,7) ");
        if (StringUtils.isNotBlank(params.get("ids"))) {
            sql.append(" and id in (").append(params.get("ids")).append(") ");
        } else {
            sql.append(" and batchId in (").append(params.get("batchIds")).append(") ");
        }
        return sql.toString();
    }
}
